#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into hive.edu_dws.kxh_dws_web_chat_ems_day_cnt
with tmp as (select
       --维度
       substring (create_time,1,10) as create_time_day,-- 天
       area,--地域
       seo_source,-- 搜素来源
       origin_channel,-- 投放渠道
       from_url,-- 会话来源页面
     -- 指标的字段
       session_id ,
      sid ,
       ip,
    -- 条件
     msg_count ,
     --去重
     row_number() over(partition by sid ) as sid_rn,
      row_number() over(partition by session_id ) as session_id_rn,
      row_number() over(partition by ip) as ip_rn
from edu_dwb.kxh_dwb_web_chat_ems_wide
)
select
       create_time_day,
       area,
       seo_source,
       origin_channel,
       from_url,
       --分组标记
        case when grouping (area)=0 then  area
             when  grouping (origin_channel)=0 then origin_channel
             when  grouping (seo_source)=0 then seo_source
             when  grouping (from_url)=0 then from_url
             when grouping (create_time_day)=0 then 'all'
             else 'other' end as group_typ,
       --日统计
       --总访问Session个数
       case when grouping(area)=0 then count(if(session_id_rn=1 and area is not null and msg_count>0,session_id,null))
            when  grouping(origin_channel)=0 then count(if(session_id_rn=1 and origin_channel is not null and msg_count>0,session_id,null))
            when  grouping(seo_source)=0 then count(if(session_id_rn=1 and seo_source is not null and msg_count>0,session_id,null))
            when  grouping(from_url)=0 then count(if(session_id_rn=1 and from_url is not null and msg_count>0,session_id,null))
            when   grouping(create_time_day)=0 then count(if(session_id_rn=1 and create_time_day is not null and msg_count>0,session_id,null))
             else 0 end  as session_id_count,
       --总访问用户量
          case when grouping(area)=0 then count(if(sid_rn=1 and area is not null,sid,null))
            when grouping(origin_channel)=0 then count(if(sid_rn=1 and origin_channel is not null,sid,null))
            when grouping(seo_source)=0 then count(if(sid_rn=1 and seo_source is not null,sid,null))
            when grouping(from_url)=0 then count(if(sid_rn=1 and from_url is not null,sid,null))
            when grouping(create_time_day)=0 then count(if(sid_rn=1 and create_time_day is not null,sid,null))
             else 0 end  as sid_count,
       --总访问IP个数 ip_count
    case when grouping(area)=0 then count(if(ip_rn=1 and area is not null,ip,null))
            when grouping(origin_channel)=0 then count(if(ip_rn=1 and origin_channel is not null,ip,null))
            when grouping(seo_source)=0 then count(if(ip_rn=1 and seo_source is not null,ip,null))
            when grouping(from_url)=0 then count(if(ip_rn=1 and from_url is not null,ip,null))
            when grouping(create_time_day)=0 then count(if(ip_rn=1 and create_time_day is not null,ip,null))
             else 0 end  as ip_count,
      -- 咨询率
       case when count(if(sid_rn=1 and area is not null,sid,null))>0 AND
        grouping(area)=0 then count(if(session_id_rn=1 and area is not null and msg_count>0,session_id,null))*1.00 / count(if(sid_rn=1 and area is not null,sid,null))
     end as con_rate
from tmp
group by
grouping sets (
       (create_time_day),
       (create_time_day,area),
       (create_time_day,origin_channel),
       (create_time_day,seo_source),
       (create_time_day,from_url)
    );
"